package com.shopping.category;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;

import com.shopping.category.*;
import com.shopping.util.*;

public class CategoryDAO {
	public static void save(Category c) {
		Connection conn = null;
		String sql = "";
		PreparedStatement prepStat = null;
		try {
			conn = DB.getConn();
			if(c.getId() == -1) {
				sql = "insert into category values (null,?,?,?,?,?)";
			} else {
				sql = "insert into category values (c.getId(),?,?,?,?,?)";
				
			}
			prepStat = DB.prepStat(conn,sql);
			prepStat.setInt(1,c.getPid());
			prepStat.setString(2,c.getName());
			prepStat.setString(3,c.getDescr());
			prepStat.setInt(4,c.isCno() ? 0 : 1);
			prepStat.setInt(5,c.getGrade());
			prepStat.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closePrepStat(prepStat);
			DB.closeConn(conn);
		}
	}
	
	public static void getCategories(List<Category> list,int id) {
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from category where pid = " + id;
			rs = DB.executeQuery(conn, sql);
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setPid(rs.getInt("pid"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setCno(rs.getInt("cno") == 0?true:false);
				c.setGrade(rs.getInt("grade"));
				list.add(c);
				if(!c.isCno()) {
					getCategories(list,c.getId());
				}
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeRs(rs);
			DB.closeConn(conn);
		}
	}

	public static void addChildCategory(int pid,String name,String descr) {
		Connection conn = null;
		PreparedStatement prepStat = null;
		ResultSet rs = null;
		Statement stat = null;
		
		try {
			conn = DB.getConn();
			
			rs = DB.executeQuery(conn,"select * from category where id = " + pid );
			System.out.println("select * from category where id = " + pid );
			int parentGrade = 0;
			if(rs.next()) {
				parentGrade = rs.getInt("grade");
			
			}
			//存储新的Category
			conn.setAutoCommit(false);
			String sql = "insert into category values (null,?,?,?,?,?)";
			prepStat = DB.prepStat(conn,sql);
			
			prepStat.setInt(1,pid);
			prepStat.setString(2,name);
			prepStat.setString(3,descr);
			prepStat.setInt(4,0);
			prepStat.setInt(5,parentGrade+1);
			prepStat.executeUpdate();
			
			
			//更新父节点，设置为非叶子节点
			stat = conn.createStatement();//DB.getStat(conn);x
			DB.executeUpdate(stat, "update category set cno = 1 where id = " + pid);
			System.out.println("update category set cno = 1 where id = " + pid);
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DB.closePrepStat(prepStat);
			DB.closeConn(conn);
		}
	}

	public static Category loadById(int id) {
		Connection conn = null;
		ResultSet rs = null;
		Category c = null; 
		
		try {
			conn = DB.getConn();
			rs = DB.executeQuery(conn, "select * from Category where id = " + id);
			if(rs.next()) {
				c = new Category();
				c.setId(rs.getInt("id"));
				c.setPid(rs.getInt("pid"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setCno(rs.getInt("cno") == 0?true:false);
				c.setGrade(rs.getInt("grade"));
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeRs(rs);
			DB.closeConn(conn);
		}
		return c;
   }

	public static void deleteCategories(int id) {
		Connection conn = null;
		ResultSet rs = null;
		ResultSet rsPid = null;
		Statement stat = null;
		try {
			conn = DB.getConn();
			stat = DB.getStat(conn);
			
			Category c = loadById(id);
			String sql = "select * from category where pid = " + id;
			rs = DB.executeQuery(conn, sql);
			while(rs.next()) {
				deleteCategories(rs.getInt("id"));	
			}
			
			stat.executeUpdate("delete from category where id =" + id);
			//验证并改变父节点是否任为叶子接点
			rsPid = DB.executeQuery(conn, "select * from category where pid = " + c.getPid());
			if(!rsPid.next()) {
				stat.executeUpdate("update category set cno = 0   where id = " + c.getPid());
			} 
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeRs(rs);
			DB.closeConn(conn);
		}
	}
		
	
   


}
	
	



